#!/bin/bash

#检测是否输入日期
if [ $1 -lt 1 ] ; then
echo '请输入要查询的日期：'
fi

#将输入的日期赋值
txdate=$1


#创建客户临时表
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.dws_temp_bh_cust (cif_no string,rece_pay_flag string,su_cny double,su_usd double);"

#向客户临时表里插入数据
hive -e "set hive.exec.mode.local.auto=true;truncate table daekeyijiance.dws_temp_bh_cust;insert into daekeyijiance.dws_temp_bh_cust (cif_no,rece_pay_flag,su_cny,su_usd) select t.cif_no ,t.rece_pay_flag,t.su_cny,t.su_usd from(SELECT cif_no,rece_pay_flag,sum(tx_cny_amt) su_cny,sum(tx_usd_amt) su_usd from daekeyijiance.dwd_tb_cust_tx  where 1 = 1 and ct_ind = '1'  and tx_date = to_date('$txdate')   group by  cif_no,rece_pay_flag) t where t.su_cny >= 50000 or t.su_usd >= 10000;"

#创建临时交易表
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.dws_temp_bh_tx (trace_no string,cif_no string,cif_name string,tx_date date,modell_type string,su_amt double,cn_tra double);"

#向临时交易表中插入数据
hive -e "set hive.exec.mode.local.auto=true;truncate table daekeyijiance.dws_temp_bh_tx;insert into daekeyijiance.dws_temp_bh_tx (trace_no,cif_no,cif_name,tx_date,modell_type,su_amt,cn_tra) select t1.trace_no,t1.cif_no,t1.cif_name,t1.tx_date,'0501',sum(t1.tx_amt) over (partition by t1.cif_no),count(t1.trace_no) over (partition by t1.cif_no) from daekeyijiance.dwd_tb_cust_tx t1 join daekeyijiance.dws_temp_bh_cust t2 on t1.cif_no = t2.cif_no and t1.rece_pay_flag = t2.rece_pay_flag where t1.tx_date = to_date('$txdate') and t1.ct_ind = '1';"

#创建大额案例客户信息表
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.dws_tb_bh_case_cust(case_no string,case_dt date,cif_no string,cif_name string,input_flag string,modell_type string,su_amt double,cn_tra double,tx_date date,is_rptf string);"

#向大额案例客户信息表中插入数据
hive -e "set hive.exec.mode.local.auto=true;truncate table daekeyijiance.dws_tb_bh_case_cust;insert into daekeyijiance.dws_tb_bh_case_cust (case_no,case_dt,cif_no,cif_name,input_flag,modell_type,su_amt,cn_tra,tx_date,is_rptf) select 'BH'||cast(current_date as string)||cif_no||modell_type ,current_date as case_dt,cif_no,cif_name,'1',modell_type,su_amt,cn_tra,tx_date,'1' from daekeyijiance.dws_temp_bh_tx group by cif_no,cif_name,modell_type,su_amt,cn_tra,tx_date;"

#创建大额案例交易信息表
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.dws_tb_bh_case_tx (case_no string,case_dt date,trace_no string,cif_no string,cif_name string,ac_id string,tx_br_no string,ct_ind string,rece_pay_flag string,tx_amt double,tx_cny_amt double,tx_usd_amt double,tx_date date,tx_time timestamp,local_flag string,cb_flag string,input_flag string,modell_type string,is_rptf string);"

#向大额案例交易信息表中插入数据
hive -e "set hive.exec.mode.local.auto=true;truncate table daekeyijiance.dws_tb_bh_case_tx;insert into daekeyijiance.dws_tb_bh_case_tx (case_no,case_dt,trace_no,cif_no,cif_name,ac_id,tx_br_no,ct_ind,rece_pay_flag,tx_amt,tx_cny_amt,tx_usd_amt,tx_date,tx_time,local_flag,cb_flag,input_flag,modell_type,is_rptf) select 'BH'||cast(current_date as string)||t1.cif_no||t1.modell_type as case_no,current_date as case_dt,t1.trace_no,t1.cif_no,t1.cif_name,t2.ac_id,t2.tx_br_no,t2.ct_ind,t2.rece_pay_flag ,t2.tx_amt,t2.tx_cny_amt,t2.tx_usd_amt,t2.tx_date,t2.tx_time,t2.local_flag,t2.cb_flag,'1',t1.modell_type,'1' from daekeyijiance.dws_temp_bh_tx t1 join  daekeyijiance.dwd_tb_cust_tx t2 on t1.trace_no = t2.trace_no where t2.tx_date = to_date('$txdate');"

